<?php

class ReportGeneralClass 
{
	public $_explicitType = "presstrack.ReportGeneralClass";
    
	public $iNumOfOrder		= 0;
	public $dSumOfOrder		= 0;
	public $dSumOfPayment	= 0;
	public $dSumOfDebt		= 0;
	
	public $arManagerBySaleAndPayment;
	public $arCustomerByPaymentAndDebt;	
	public $arPressTaskByCost;
	public $arPostpressTaskByCost;
		
	static function GetGeneralReport($year, $month)
	{
		set_time_limit(0);	
		$data = $month . '/' . $year;		
		$result->status = false;
		$report = new ReportGeneralClass();
		
		$sql = "SELECT idorder FROM press_track_new.order WHERE creation LIKE '%$data'";
		$query = mysql_query($sql);
		if (mysql_errno())
		{
			$result->message = mysql_errno() . ": " . mysql_error();
			return $result;
		}		
		
		// num of order	
		while ($row = mysql_fetch_array($query, MYSQL_NUM)) 
		{
			$array_of_orders[] = $row[0];
			$report->iNumOfOrder++;
		}				
		
		// sum of order
		$sql = "SELECT SUM(cost) AS sum_of_order FROM task "
			 . "WHERE task.order IN (" . implode(',', $array_of_orders) . ")";
		$query = mysql_query($sql);		
		if (mysql_errno())
		{
			$result->message = mysql_errno() . ": " . mysql_error();
			return $result;
		}
		$res = mysql_fetch_object($query);
		$report->dSumOfOrder = round($res->sum_of_order, 2);		
		
		// sum of payment
		$sql = "SELECT SUM(sum) AS sum_of_payment FROM payment "
			 . "WHERE press_track_new.payment.creation LIKE '%$data'";
		$query = mysql_query($sql);		
		if (mysql_errno())
		{
			$result->message = mysql_errno() . ": " . mysql_error();
			return $result;
		}	 
		$res = mysql_fetch_object($query);
		$report->dSumOfPayment = round($res->sum_of_payment, 2);
		
		// sum of debt
		$sql = "SELECT SUM(debt) AS sum_of_debt FROM press_track_new.order "
			 . "WHERE creation LIKE '%$year'";
		$query = mysql_query($sql);		
		if (mysql_errno())
		{
			$result->message = mysql_errno() . ": " . mysql_error();
			return $result;
		}	 
		$res = mysql_fetch_object($query);
		$report->dSumOfDebt = round($res->sum_of_debt, 2);
		
		// for dia
		
		// for managers: sum of sales, sum of payments 
		$sql = "SELECT iduser, name FROM user WHERE medium = -1 AND section = 1";		
		$query = mysql_query($sql);		
		if (mysql_errno())
		{
			$result->message = mysql_errno() . ": " . mysql_error();
			return $result;
		}
		
		while ($manager = mysql_fetch_object($query)) 
		{
			$sql = "SELECT press_track_new.order.idorder FROM press_track_new.order "
				 . "WHERE manager = $manager->iduser AND creation LIKE '%$data'";
			$query_orders = mysql_query($sql);
			if (mysql_errno())
			{
				$result->message = mysql_errno() . ": " . mysql_error();
				return $result;
			}		
			
			$array_of_orders2 = array();
			while ($row = mysql_fetch_array($query_orders, MYSQL_NUM)) 
			{
				$array_of_orders2[] = $row[0];
			}
			
			if (count($array_of_orders2) > 0)
			{
				// sum of sales
				$sql = "SELECT SUM(cost) AS sum_of_sales FROM task "
					 . "WHERE task.order IN(" 
					 . implode(',', $array_of_orders2) 
					 . ")";
				$query_sales = mysql_query($sql);
				$res = mysql_fetch_object($query_sales);	
				$manager->first = round($res->sum_of_sales, 2);
			
				// sum of payments 
				$sql = "SELECT SUM(sum) AS sum_of_payments FROM payment "
					 . "WHERE payment.order IN(" 
					 . implode(',', $array_of_orders2) 
					 . ")";
				$query_payments = mysql_query($sql);
				$res = mysql_fetch_object($query_payments);	
				$manager->second = round($res->sum_of_payments, 2);
								
				$report->arManagerBySaleAndPayment[] = $manager;
			}
		}
		
		// for customers: sum of payments, sum of debt 
		$sql = "SELECT DISTINCT customer AS iduser, name AS name "
			 . "FROM press_track_new.order, press_track_new.user "
			 . "WHERE press_track_new.order.idorder IN (" 
			 . implode(',', $array_of_orders) 
			 . ") AND user.iduser = customer";
		$query = mysql_query($sql);		
		if (mysql_errno())
		{
			$result->message = mysql_errno() . ": " . mysql_error();
			return $result;
		}
		
		while ($customer = mysql_fetch_object($query)) 
		{
			$sql = "SELECT press_track_new.order.idorder FROM press_track_new.order "
				 . "WHERE customer = $customer->iduser AND creation LIKE '%$data'";
			$query_orders = mysql_query($sql);
			if (mysql_errno())
			{
				$result->message = mysql_errno() . ": " . mysql_error();
				return $result;
			}		
			
			$array_of_orders3 = array();
			while ($row = mysql_fetch_array($query_orders, MYSQL_NUM)) 
			{
				$array_of_orders3[] = $row[0];
			}
			
			// sum of payments
			$sql = "SELECT SUM(sum) AS sum_of_payments FROM payment "
				 . "WHERE payment.order IN(" 
				 . implode(',', $array_of_orders3) 
				 . ")";
			$query_payments = mysql_query($sql);
			if (mysql_errno())
			{
				$result->message = mysql_errno() . ": " . mysql_error();
				return $result;
			}
			$res = mysql_fetch_object($query_payments);	
			$customer->first = round($res->sum_of_payments, 2);
			
			// sum of debt
			$sql = "SELECT SUM(debt) AS sum_of_debt FROM press_track_new.order "
				 . "WHERE creation LIKE '%$year' "
				 . "AND press_track_new.order.customer = $customer->iduser";
			$query_debt = mysql_query($sql);
			if (mysql_errno())
			{
				$result->message = mysql_errno() . ": " . mysql_error();
				return $result;
			}
			$res = mysql_fetch_object($query_debt);	
			$customer->second = round($res->sum_of_debt, 2);
			
			if ($customer->first > 0 && $customer->second > 0)
			{
				$report->arCustomerByPaymentAndDebt[] = $customer;
			}
		}
		
		// sum of task cost
		
		// SM-52
		$sql = "SELECT SUM(cost) AS sum_of_sm52 FROM task "
			 . "WHERE value LIKE '%SM-52%' AND section = 4 "
			 . "AND task.order IN (" 
			 . implode(',', $array_of_orders) 
			 . ")";
		$query = mysql_query($sql);		
		if (mysql_errno())
		{
			$result->message = mysql_errno() . ": " . mysql_error();
			return $result;
		}
		$res = mysql_fetch_object($query);
		$press_row->name	= 'SM-52';
		$press_row->first 	= round($res->sum_of_sm52, 2);
		$press_row->second 	= round($res->sum_of_sm52, 2);
		
		$report->arPressTaskByCost[] = clone $press_row;
		
		// SM-74
		$sql = "SELECT SUM(cost) AS sum_of_sm74 FROM task "
			 . "WHERE value LIKE '%SM-74%' AND section = 4 "
			 . "AND task.order IN (" 
			 . implode(',', $array_of_orders) 
			 . ")";
		$query = mysql_query($sql);		
		if (mysql_errno())
		{
			$result->message = mysql_errno() . ": " . mysql_error();
			return $result;
		}
		$res = mysql_fetch_object($query);
		$press_row->name	= 'SM-74';
		$press_row->first 	= round($res->sum_of_sm74, 2);
		$press_row->second 	= round($res->sum_of_sm74, 2);
		
		$report->arPressTaskByCost[] = clone $press_row;
		
		// Ryobi
		$sql = "SELECT SUM(cost) AS sum_of_ryobi FROM task "
			 . "WHERE value LIKE '%Ryobi%' AND section = 4 "
			 . "AND task.order IN (" 
			 . implode(',', $array_of_orders) 
			 . ")";
		$query = mysql_query($sql);		
		if (mysql_errno())
		{
			$result->message = mysql_errno() . ": " . mysql_error();
			return $result;
		}
		$res = mysql_fetch_object($query);
		$press_row->name	= 'Ryobi';
		$press_row->first 	= round($res->sum_of_ryobi, 2);
		$press_row->second 	= round($res->sum_of_ryobi, 2);
		
		$report->arPressTaskByCost[] = clone $press_row;
		
		// postpress		
		$sql = "SELECT label FROM press_track_new.operation WHERE section = 5";
		$query = mysql_query($sql);		
		if (mysql_errno())
		{
			$result->message = mysql_errno() . ": " . mysql_error();
			return $result;
		}
		
		while ($operation = mysql_fetch_object($query)) 
		{
			$sql = "SELECT SUM(cost) AS total FROM press_track_new.task "
				 . "WHERE press_track_new.task.order IN ("
				 . implode(',', $array_of_orders)
				 . ") AND task.label = '$operation->label'";
			$query_postpress = mysql_query($sql);
			if (mysql_errno())
			{
				$result->message = mysql_errno() . ": " . mysql_error();
				return $result;
			}
			$row = mysql_fetch_object($query_postpress);
			if ($row->total > 0) 
			{
				$operation_row->name 	= $operation->label;
				$operation_row->first 	= round($row->total, 2);
				$operation_row->second	= round($row->total, 2);
				$report->arPostpressTaskByCost[] = clone $operation_row;
			}			
		}
				
		$result->status = true;
		$result->report = $report;
		return $result;
	}	
}
?>